import datetime
import requests
import pandas as pd
import json
from datetime import datetime
from spider.data_source.jsl.jsl_login import get_cookie_str
from spider.data_source.jsl.jsl_url import *
from utils.table_data_tools import adjust_condition_concat
from utils.pd_show_utils import df_table
from spider.data_source.jsl.jsl_data_map import alive_bond_map


def get_bond_info(pp=False):
    """
    获取总体转债行情数据 中位数等
    :return:
    """
    response = requests.get(
        url=GET_BOND_INFO,
        headers={
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    result = json.loads(response.text)
    if pp:
        print(result['data'])
    return result['data']


def get_dead_bond(db_instance, pp=False, table_name='jsl_bonds_dead'):
    """
    JSL接口
    *通过jsl接口获得已退市可转债的信息
    :return:写入JSL_bonds_dead表
    """
    # 此接口不需要cookie
    response = requests.post(
        url=GET_DEAD_BOND_URL,
        headers={
            # "Cookie": get_cookie_str(),
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    # 直接请求接口
    result = json.loads(response.text)
    cell_list = map(lambda row: row['cell'], result['rows'])
    pd_dead_bonds = pd.DataFrame(cell_list)
    if pp:
        df_table(pd_dead_bonds)
    pd_dead_bonds.to_sql(table_name,
                         con=db_instance.engine,
                         if_exists='replace',
                         index=False)


def get_alive_bond(db_instance, name_tail=None, table_name='jsl_bonds_alive', pp=False):
    """
    JSL接口
    *通过jsl接口获得现存可转债信息
    包含jsl刷新登录
    :return:
    """
    cookie = get_cookie_str(db_instance)
    if cookie is None:
        print('cookie error')
        return None

    response2 = _request_post(GET_BIG_ALIVE_BOND_URL, cookie)
    cell_list2 = json.loads(response2.text)['data']
    pd_result = pd.DataFrame(cell_list2)
    if pp:
        # pd_result.drop(['t_flag', 'owned', 'hold',
        #                 'option_value', 'put_ytm_rt', 'notes',
        #                 'noted', 'last_time', 'qstatus', 'sqflag',
        #                 'ref_yield_info', 'price_tips', 'option_tip',
        #                 'pct_chg', 'adjust_orders', 'redeem_orders', 'custom_formula1001'], axis=1, inplace=True)
        # pd_result.rename(columns=alive_bond_map, inplace=True)
        # pd_result.to_csv("alive.csv")
        df_table(pd_result)
    pd_result['icons'] = pd_result['icons'].astype('str')
    pd_result['t_flag'] = pd_result['t_flag'].astype('str')
    from jsl_data_map import alive_bond_map
    pd_result = pd_result.rename(columns=alive_bond_map)
    pd_result.to_csv("alive.csv")

    del pd_result['custom_formula1001']
    del pd_result['option_value']
    pd_result.to_sql(table_name,
                     con=db_instance.engine,
                     if_exists='replace', index=False)

    # 如果有后缀将数据存入日周期总表
    if name_tail is not None:
        table_name = table_name + '_' + name_tail
        pd_result.to_sql(table_name,
                         con=db_instance.engine,
                         if_exists='replace', index=False)


def get_redeem_bond(db_instance, pp=False):
    """
    JSL接口
    *获取强赎信息
    :return:
    """
    # 此接口不需要cookie
    response = requests.post(
        url=GET_REDEEM_BOND_URL,
        headers={
            "Cookie": get_cookie_str(db_instance),
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    cell_list1 = json.loads(response.text)['data']

    pd_redeem = pd.DataFrame(cell_list1)
    pd_redeem['icons'] = pd_redeem['icons'].astype('str')

    if pp:
        df_table(pd_redeem)
    pd_redeem.to_sql('jsl_bonds_redeem',
                     con=db.engine,
                     if_exists='replace',
                     index=False)
    return


def get_adjust_log(db_instance, bond_id, log_type, pp=False):
    """
    获取下修记录数据后用于前端实时展示，因为历史数据太多了
    :param pp:
    :param db_instance:
    :param bond_id:
    :param log_type:U(不下修记录)和 D(下修记录)
    :return:
    """
    response = requests.post(
        url=GET_BOND_ADJUST_LOG % (bond_id, log_type),
        headers={
            "Cookie": get_cookie_str(db_instance),
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    adjust_log_list = json.loads(response.text)['data']
    pd_adjust_log = pd.DataFrame(adjust_log_list)
    if pp:
        df_table(pd_adjust_log)
    if len(pd_adjust_log) > 0:
        pd_adjust_log.to_sql('bond_adjust_log',
                             con=db.engine,
                             if_exists='append',
                             index=False)


def get_adjust_bond(db_instance, pp=False):
    """
    *获取整体下修信息
    数据库每天更新一次足够
    :return:
    """
    # 此接口不需要cookie
    response = requests.post(
        url=GET_ADJUST_BOND_URL,
        headers={
            "Cookie": get_cookie_str(db_instance),
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    cell_list1 = json.loads(response.text)['data']
    pd_adjust = pd.DataFrame(cell_list1)
    pd_adjust = adjust_condition_concat(pd_adjust)
    pd_adjust['icons'] = pd_adjust['icons'].astype('str')

    if pp:
        df_table(pd_adjust)
    pd_adjust.to_sql('bond_adjust_table',
                     con=db.engine,
                     if_exists='replace',
                     index=False
                     )


def get_pre_bond(db_instance, pp=False):
    """
    获取待发可转债信息
    :return:
    """
    response = requests.get(
        url=GET_BOND_PRE,
        headers={
            "Cookie": get_cookie_str(db_instance),
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    # 直接请求接口
    result = json.loads(response.text)['data']
    pd_pre = pd.DataFrame(result)
    if pp:
        df_table(pd_pre)
    pd_pre.to_sql('pd_pre',
                  con=db.engine,
                  if_exists='replace',
                  index=False)


def get_bond_detail_history(db_instance, symbol, pp=False):
    """
    通过集思录获得可转债的详细历史数据
    :param pp:
    :param db_instance:
    :param symbol:可转债代码 不带sh或sz
    :return:
    """
    url = JSL_API % (symbol, int(datetime.now().timestamp() * 1000))
    headers = {
        'cookie': get_cookie_str(db_instance)
    }
    result = requests.post(url, headers=headers)
    result_dict = json.loads(result.content)
    data_list = []
    for item in result_dict['rows']:
        data_list.append(item['cell'])
    columns = ['date', 'volume', 'price',
               'stock_volume', 'sprice',
               'convert_value', 'ytm_rt', 'premium_rate',
               'curr_iss_amt', 'turnover_rt']

    if len(data_list) > 0:
        dt = pd.DataFrame(pd.DataFrame(data_list))
        dt = dt.rename(columns={'last_chg_dt': 'date', "premium_rt": "premium_rate"})
        dt = dt[columns]

        if pp:
            df_table(dt)
        return dt
    else:
        return None


def _request_post(url, cookie):
    """
    用于指定JSL_alive_bond的字段
    :param url:
    :param cookie:
    :return:
    """
    response = requests.post(
        url=url,
        headers={
            "Columns": "1,70,2,3,5,6,7,8,9,10,11,12,14,15,"
                       "77,78,79,80,81,82,83,84,16,17,18,19,20,21,22,23,"
                       "24,72,25,26,27,28,29,30,31,32,33,34,35,"
                       "36,75,44,46,47,50,52,53,74,73,54,55,56,57,"
                       "58,59,60,61,62,76,63,64,67,71,1001",
            "Init": "1",
            "Cookie": cookie,
            "Content-Type": "application/json; charset=utf-8",
        },
    )
    return response


def temp_bond(db_instance, url):
    """
    调试用的一个没什么实际作用的方法
    :return:
    """
    cookie = get_cookie_str(db_instance)
    if cookie is None:
        print('cookie error')
        return None
    response1 = _request_post(url, cookie)
    cell_list1 = json.loads(response1.text)['data']
    pd1 = pd.DataFrame(cell_list1)
    pd1.to_csv('redeem.csv', encoding='utf_8_sig')
    # print(pd1)


if __name__ == '__main__':
    from db.sql_db import DbHandler

    db = DbHandler()
    db.create_session()

    # get_bond_info(True)
    # get_dead_bond(db, pp=True)
    get_alive_bond(db, pp=True)
    # get_redeem_bond(db,pp=True)
    # get_adjust_log(db,bond_id='123044', log_type='D', pp=True)
    # get_adjust_bond(db,pp=True)
    # get_bond_detail_history(db_instance=db, symbol='123044', pp=True)
    # get_pre_bond(db, pp=True)
